﻿using System;
using System.Data;
using System.Drawing;
using System.IO;
using System.Text;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace Eff.Web
{
    public class Excel
    {
        /// <summary>
        /// 把DataTable转成Xls文件
        /// </summary>
        /// <param name="dt">DataTable</param>
        /// <param name="xlsTempAddress">保存地址</param>
        /// <param name="encoding"></param>
        /// <returns></returns>
        public static string SaveTableToXLS(DataTable dt, string xlsTempAddress, Encoding encoding)
        {

            var stringWriter = new StringWriter();
            var htmlWriter = new HtmlTextWriter(stringWriter);
            var excel = new DataGrid();
            var alternatingStyle = new TableItemStyle();
            var headerStyle = new TableItemStyle();
            var itemStyle = new TableItemStyle();
            alternatingStyle.BackColor = Color.LightGray;
            headerStyle.BackColor = Color.LightGray;
            headerStyle.Font.Bold = true;
            headerStyle.HorizontalAlign = HorizontalAlign.Center;
            itemStyle.HorizontalAlign = HorizontalAlign.Center;

            excel.ItemDataBound += ExcelItemDataBound;
            excel.AlternatingItemStyle.MergeWith(alternatingStyle);
            excel.HeaderStyle.MergeWith(headerStyle);
            excel.ItemStyle.MergeWith(itemStyle);
            excel.GridLines = GridLines.Both;
            excel.HeaderStyle.Font.Bold = true;
            excel.DataSource = dt.DefaultView; //输出DataTable的内容
            excel.DataBind();
            excel.RenderControl(htmlWriter);
            if (!Directory.Exists(xlsTempAddress))
                Directory.CreateDirectory(xlsTempAddress);
            var rad = new Random();
            int i = rad.Next(100);
            string filestr = xlsTempAddress + DateTime.Now.ToString("yyyyMMddhhmmss") + i + ".xls"; //filePath是文件的路径
            var sw = new StreamWriter(filestr, false, encoding);
            sw.Write(stringWriter.ToString());
            sw.Close();
            return filestr;
        }
        private static void ExcelItemDataBound(object sender, DataGridItemEventArgs e)
        {
            e.Item.Cells[1].Attributes.Add("style", "vnd.ms-excel.numberformat:@;");
            e.Item.Cells[2].Attributes.Add("style", "vnd.ms-excel.numberformat:@;");
            e.Item.Cells[3].Attributes.Add("style", "vnd.ms-excel.numberformat:@;");
            e.Item.Cells[4].Attributes.Add("style", "vnd.ms-excel.numberformat:@;");
            e.Item.Cells[5].Attributes.Add("style", "vnd.ms-excel.numberformat:@;");
            e.Item.Cells[6].Attributes.Add("style", "vnd.ms-excel.numberformat:@;");
            e.Item.Cells[7].Attributes.Add("style", "vnd.ms-excel.numberformat:@;");
        }
    }
}
